import pandas as pd
import os
import glob
from datetime import datetime
from collections import defaultdict

def find_common_sns_with_timerange(label_file_path, logs_dir_path, output_file_path):
    """
    找出标签文件和日志文件中共同存在的SN号，
    并计算每个SN号在日志文件中的时间范围，保存到CSV文件
    
    参数:
    label_file_path: 标签文件路径
    logs_dir_path: 日志文件所在目录
    output_file_path: 输出CSV文件路径
    """
    # 读取标签文件
    print("读取标签文件...")
    label_df = pd.read_csv(label_file_path)
    
    # 获取标签文件中的SN号集合
    label_sn_set = set(label_df['disk_sn'].unique())
    print(f"标签文件中共有 {len(label_sn_set)} 个独特SN号")
    
    # 获取所有日志文件路径
    log_files = glob.glob(os.path.join(logs_dir_path, "*.csv"))
    print(f"发现 {len(log_files)} 个日志文件")
    
    # 在日志文件中找到的SN号集合
    log_sn_set = set()
    
    # 用于存储每个SN号的时间范围
    sn_time_ranges = defaultdict(lambda: {'min_time': None, 'max_time': None})
    
    # 处理每个日志文件
    for i, log_file in enumerate(log_files):
        print(f"处理日志文件 {i+1}/{len(log_files)}: {os.path.basename(log_file)}")
        try:
            # 读取日志文件
            log_df = pd.read_csv(log_file)
            
            # 确保dt列存在
            if 'dt' not in log_df.columns:
                print(f"  警告：文件中没有dt列: {os.path.basename(log_file)}")
                continue
                
            # 收集此文件中的SN号及其时间范围
            for sn in log_df['log_sn'].unique():
                log_sn_set.add(sn)
                
                # 获取该SN号的所有记录
                sn_records = log_df[log_df['log_sn'] == sn]
                
                # 转换时间字符串为datetime对象
                try:
                    sn_records['dt'] = pd.to_datetime(sn_records['dt'])
                    
                    # 获取最早和最晚时间
                    min_time = sn_records['dt'].min()
                    max_time = sn_records['dt'].max()
                    
                    # 更新该SN号的时间范围
                    if sn_time_ranges[sn]['min_time'] is None or min_time < sn_time_ranges[sn]['min_time']:
                        sn_time_ranges[sn]['min_time'] = min_time
                        
                    if sn_time_ranges[sn]['max_time'] is None or max_time > sn_time_ranges[sn]['max_time']:
                        sn_time_ranges[sn]['max_time'] = max_time
                except Exception as e:
                    print(f"  警告：处理SN {sn}的时间数据时出错: {e}")
                
        except Exception as e:
            print(f"  处理文件时出错: {e}")
    
    # 找出共有的SN号
    common_sns = label_sn_set.intersection(log_sn_set)
    
    print("\n结果:")
    print(f"标签文件中SN号数量: {len(label_sn_set)}")
    print(f"日志文件中SN号数量: {len(log_sn_set)}")
    print(f"两者共有的SN号数量: {len(common_sns)}")
    
    if common_sns:
        # 准备保存结果
        result_data = []
        for sn in sorted(common_sns):
            min_time = sn_time_ranges[sn]['min_time']
            max_time = sn_time_ranges[sn]['max_time']
            
            min_time_str = min_time.strftime('%Y-%m-%d %H:%M:%S') if min_time else 'N/A'
            max_time_str = max_time.strftime('%Y-%m-%d %H:%M:%S') if max_time else 'N/A'
            
            result_data.append({
                'common_sn': sn,
                'first_seen': min_time_str,
                'last_seen': max_time_str
            })
        
        # 将结果保存到CSV
        result_df = pd.DataFrame(result_data)
        result_df.to_csv(output_file_path, index=False)
        print(f"\n共有的SN号及其时间范围已保存至 {output_file_path}")
        
        # 显示部分结果作为示例
        sample_size = min(5, len(result_data))
        print(f"\n以下是部分共有的SN号及其时间范围示例 (共{len(common_sns)}个):")
        for i in range(sample_size):
            sn_data = result_data[i]
            print(f"SN: {sn_data['common_sn']}, 时间范围: {sn_data['first_seen']} 至 {sn_data['last_seen']}")
        if len(common_sns) > 5:
            print("...")
    else:
        print("未找到共有的SN号")

if __name__ == "__main__":
    # 直接在代码中指定路径
    label_file_path = "D:/data/labels.csv"  # 请修改为您的标签文件路径
    logs_dir_path = "D:/data/logs/"         # 请修改为您的日志文件目录路径
    output_file_path = "D:/data/common_sns_with_timerange.csv"  # 请修改为您的输出文件路径
    
    # 执行查找
    find_common_sns_with_timerange(label_file_path, logs_dir_path, output_file_path)